package com.aaa.qy127.dao.impl;

import com.aaa.qy127.dao.DeptDao;
import com.aaa.qy127.entity.Dept;
import com.aaa.qy127.util.JDBCUtil;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository("deptDaoImpl")
public class DeptDaoImpl implements DeptDao {
    @Override
    public List<Map<String, Object>> getAllDept() {
        String sql ="select deptid,deptNo,dname,remark,`status` from t_dept;";
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql);
        if (maps.size() > 0){
            return maps;
        }
        return null;
    }

    /*添加*/
    @Override
    public int insertDept(Dept dept) {
        String sql = "insert into t_dept values(null,?,?,?,?)";
        int insert = JDBCUtil.update(sql, dept.getDeptNo(), dept.getDname(), dept.getRemark(), dept.getStatus());
        return insert;
    }
    /*修改*/
    @Override
    public int updateDept(Dept dept) {
        String sql = "update t_dept set deptNo=?,dname=?,remark=?,status=? where deptid=?";
        int update = JDBCUtil.update(sql, dept.getDeptNo(), dept.getDname(), dept.getRemark(), dept.getStatus(), dept.getDeptid());
        return update;
    }
    /*修改状态*/
    @Override
    public int updateStatus(Integer deptid, Integer status) {
        if(status==0){
            status = 1;
        }else{
            status = 0;
        }
        String sql = "update t_dept set status=? where deptid=?";
        int i = JDBCUtil.update(sql, status,deptid);
        return 0;
    }
    /*查询所有*/
    @Override
    public List<Map<String, Object>> queryAllBook(Dept dept) {
        StringBuilder sql = new StringBuilder("SELECT d.*,count(s.deptid)  count\n" +
                "from t_dept d\n" +
                "LEFT JOIN t_staff s\n" +
                "on d.deptid = s.deptid and s.`status`=1 where 1=1");
        if (!"".equals(dept.getDeptNo()) && dept.getDeptNo()!=null){
            sql.append(" and deptNo like '%").append(dept.getDeptNo()).append("%'");
        }
        if (dept.getDname()!=null){
            sql.append(" and dname like '%").append(dept.getDname()).append("%'");
        }
        sql.append(" GROUP BY d.deptid limit ?,?");
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql.toString(),(dept.getPage()-1)*dept.getLimit(),dept.getLimit());
        if(maps.size()>0){
            return maps;
        }
        return null;
    }
    /*查询数量*/
    @Override
    public int getAllCount(Dept dept) {
        StringBuilder sql = new StringBuilder("select count(*) from t_dept where 1=1");
        if (!"".equals(dept.getDeptNo()) && dept.getDeptNo()!=null){
            sql.append(" and deptNo like '%").append(dept.getDeptNo()).append("%'");
        }
        if (dept.getDname()!=null){
            sql.append(" and dname like '%").append(dept.getDname()).append("%'");
        }
        List<List> lists = JDBCUtil.queryList(sql.toString());
        if(lists.size()>0){
            Object o = lists.get(0).get(0);
            int i = Integer.parseInt(o + "");
            return i;
        }
        return 0;
    }

    /**
     * 根据名称查信息
     * @param dname
     * @return
     */
    @Override
    public List<Map<String, Object>> getOneDept(String dname) {
        String sql = "select * from t_dept where dname = ?";
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql, dname);
        return maps;
    }

    /**
     * 根据名称和ID查信息
     * @param dname
     * @param deptid
     * @return
     */
    @Override
    public List<Map<String, Object>> getOneDeptId(String dname, Integer deptid) {
        String sql = "select * from t_dept where dname = ? and deptid=?";
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql, dname,deptid);
        return maps;
    }

    /**
     * 查询最大的部门编号
     * @return
     */
    @Override
    public int getMaxDept() {
        String sql = "select Max(deptNo) from t_dept";
        List<List> lists = JDBCUtil.queryList(sql);
        if (lists.size()>0){
            Object o = lists.get(0).get(0);
            int i = Integer.parseInt(o + "");
            return i;
        }
        return 0;
    }

}
